package com.bainaeco.framework.common.jdbc;

import java.lang.reflect.Field;
import java.sql.Time;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.bainaeco.framework.annotation.Column;
import com.bainaeco.framework.annotation.Table;
import com.bainaeco.framework.base.model.BaseModel;
import com.bainaeco.framework.enums.BaseModelTableName;
import com.bainaeco.framework.enums.SQLMapKey;
import com.bainaeco.framework.exception.PrimaryKeyIndexException;
import com.bainaeco.framework.exception.PrimaryKeyNotFoundException;
import com.bainaeco.framework.util.DateUtil;

/**
 * 
 * @author Joe Zhou
 * @param <T>
 */
public class SQLManager<T extends BaseModel> {
	
	private static String SERIAL_VERSION_UID_FIELD_NAME = "serialVersionUID";
	
	/**
	 * Get to entity value
	 * @param t
	 * @return
	 */
	@SuppressWarnings("unused")
	private Map<String, Object> getEntity(T t){
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			Table table = t.getClass().getAnnotation(Table.class);
			Field[] fields = t.getClass().getDeclaredFields();
			for(Field field : fields){
				Column column = field.getAnnotation(Column.class);
				String tableFieldName = ( column != null && !column.name().equals("") )?column.name():field.getName();
				toFirstLetterUpperCase(field.getName());
				String getMethodName = "get"+toFirstLetterUpperCase(field.getName());
				//String setMethodName = "set"+Common.getCommon().toFirstLetterUpperCase(field.getName());
				Object value = t.getClass().getMethod(getMethodName).invoke(t);
				if(value!=null){
					map.put(tableFieldName, value);
				}
			}
		}  catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}
	
	/**
	 * Get insert SQL.
	 * @param t
	 * @return
	 */
	public static <T> Map<String, Object> insert(T t){
		Map<String, Object> result = new HashMap<String, Object>();
		StringBuilder fieldBox = new StringBuilder();
		StringBuilder paramBox = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		
		Field[] fields = t.getClass().getDeclaredFields();
		for(int i=0,length=fields.length;i<length;i++){
			Field field = fields[i];
			if(!field.getName().equals(SERIAL_VERSION_UID_FIELD_NAME)){
				Object value = getValue(t,field);
				if(value != null){
					fieldBox.append("-`" + getFieldName(field) + "`-");
					paramBox.append("?,");
					list.add(getValue(t,field));
				}
			}
		}
		// get base model.
		Map<String, Object> baseMap = getInsertBaseModelAttributes(t);
		for(Map.Entry<String, Object> entry: baseMap.entrySet()) {
			if(!entry.getKey().equals(BaseModelTableName.ID.toStringValue())){
				fieldBox.append("-`" + entry.getKey() + "`-");
				list.add(entry.getValue());
			}
			paramBox.append("?,");
		}
		// add id
		fieldBox.append("-`" + BaseModelTableName.ID.toStringValue() + "`-");
		list.add(baseMap.get(BaseModelTableName.ID.toStringValue()));
		
		// get SQL of field and param.
		String tableName = getTableName(t.getClass());
		String fieldNames = fieldBox.toString().replaceAll("--", ",").replaceAll("-", "");
		String params = paramBox.toString().substring(0, paramBox.toString().length()-1);
		// get values.
		int size = list.size();
		Object[] values = (Object[])list.toArray(new Object[size]);
		
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ").append(tableName).append("(");
		sb.append(fieldNames).append(") value(").append(params).append(")");
		
		result.put(SQLMapKey.SQL.toStringValue(), sb.toString());
		result.put(SQLMapKey.VALUES.toStringValue(), values);
		return result;
	}
	
	/**
	 * Get update SQL.
	 * @param t
	 * @return
	 */
	public static <T> Map<String, Object> update(T t){
		Map<String, Object> result = new HashMap<String, Object>();
		StringBuilder fieldBox = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		
		Field[] fields = t.getClass().getDeclaredFields();
		for(int i=0,length=fields.length;i<length;i++){
			Field field = fields[i];
			if(!field.getName().equals(SERIAL_VERSION_UID_FIELD_NAME)){
				Object value = getValue(t,field);
				if(value != null){
					fieldBox.append("-`"+ getFieldName(field)).append("` = ?-");
					list.add(getValue(t,field));
				}
			}
		}
		
		// get base model.
		Map<String, Object> baseMap = getUpdateBaseModelAttributes(t);
		for(Map.Entry<String, Object> entry: baseMap.entrySet()) {
			if(!entry.getKey().equals(BaseModelTableName.ID.toStringValue())){
				fieldBox.append("-`").append(entry.getKey()).append("` = ?-");
				list.add(entry.getValue());
			}
		}
		
		// get SQL of field
		String tableName = getTableName(t.getClass());
		String fieldNames = fieldBox.toString().replaceAll("--", ",").replaceAll("-", "");
		StringBuilder sb = new StringBuilder();
		sb.append("update ").append(tableName).append(" set ");
		sb.append(fieldNames).append(" where ");
		if(baseMap.get(BaseModelTableName.ID.toStringValue())!=null){
			sb.append(BaseModelTableName.ID.toStringValue());
			sb.append(" = ?");
			list.add(baseMap.get(BaseModelTableName.ID.toStringValue()));
		}else{
			throw new PrimaryKeyNotFoundException("Primary key not found.");
		}
		// get values.
		int size = list.size();
		Object[] values = (Object[])list.toArray(new Object[size]);
		
		result.put(SQLMapKey.SQL.toStringValue(), sb.toString());
		result.put(SQLMapKey.VALUES.toStringValue(), values);
		return result;
	}
	
	public static <T> String updateToDelete(Class<T> cls){
		StringBuilder sb = new StringBuilder();
		sb.append("update ").append(getTableName(cls)).append(" set ");
		sb.append(BaseModelTableName.IS_DELETE.toStringValue()).append(" = ?");
		sb.append(" where ").append(BaseModelTableName.ID.toStringValue()).append(" = ?");
		return sb.toString();
	}
	
	/**
	 * Get delete SQL.
	 * @param t
	 * @return
	 */
	public static <T> Map<String, Object> delete(T t){
		Map<String, Object> result = new HashMap<String, Object>();
		StringBuilder sb = new StringBuilder();
		Object value = getIdValue(t);
		sb.append("delete from ").append(getTableName(t.getClass()));
		sb.append(" where ").append(BaseModelTableName.ID.toStringValue()).append(" = ?");
		result.put(SQLMapKey.SQL.toStringValue(), sb.toString());
		result.put(SQLMapKey.ID_VALUE.toStringValue(), value);
		return result;
	}
	
	public static <T> String deleteById(Class<T> cls){
		StringBuilder sb = new StringBuilder();
		sb.append("delete from ").append(getTableName(cls));
		sb.append(" where ").append(BaseModelTableName.ID.toStringValue()).append(" = ?");
		return sb.toString();
	}
	
	public static <T> String deleteByIds(Class<T> cls,int number){
		if(number <= 0)
			throw new PrimaryKeyIndexException("Primary key the number is " + number + ".");
		
		String param = "";
		for(int i = 0,length = number;i < length;i++){
			if(i!=0)
				param += ",";
			param +="?";
		}
		StringBuilder sb = new StringBuilder();
		sb.append("delete from ").append(getTableName(cls));
		sb.append(" where ").append("id in(").append(param).append(")");
		return sb.toString();
	}
	
	public static <T> Object getIdValue(T t){
		Object value = null;
		try {
			String getMethodName = "get"+toFirstLetterUpperCase(BaseModelTableName.ID.toStringValue());
			value = t.getClass().getMethod(getMethodName).invoke(t);
		} catch (Exception e) {
			throw new PrimaryKeyNotFoundException("Primary key not found of field '" + BaseModelTableName.ID.toStringValue() + "'.");
		}
		if(value == null){
			throw new NullPointerException("Primary key the value is null.");
		}
		return value;
	}
	
	public static String queryById(Class<? extends Object> cls){
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ").append(getTableName(cls)).append(" where ");
		sb.append(BaseModelTableName.ID.toStringValue()).append(" = ?");
		return sb.toString();
	}
	
	public static String getPaging(String sql){
		StringBuilder sb = new StringBuilder();
		sb.append("select a.* from (");
		sb.append(sql);
		sb.append(") a limit ?,?");
		return sb.toString();
	}
	
	private static <T> Object getValue(T t,Field field){
		String getMethodName = "get"+toFirstLetterUpperCase(field.getName());
		Object value = null;
		try {
			value = t.getClass().getMethod(getMethodName).invoke(t);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return value;
	}
	
	/**
	 * Get table name.
	 * @param cls
	 * @return
	 */
	private static String getTableName(Class<? extends Object> cls){
		String tableName = toChangeFormat(cls.getSimpleName());
		if(cls.isAnnotationPresent(Table.class)){
			Table table = cls.getAnnotation(Table.class);
			if(!table.name().equals("")){
				tableName = table.name();
			}
		}
		return tableName;
	}
	
	private static String getFieldName(Field field){
		String fieldName = toChangeFormat(field.getName());
		if(field.isAnnotationPresent(Column.class)){
			Column column = field.getAnnotation(Column.class);
			fieldName = !column.name().equals("")?column.name():fieldName;
		}
		return fieldName;
	}
	
	private static <T> Map<String, Object> getInsertBaseModelAttributes(T t){
		Map<String, Object> bma = new HashMap<String, Object>();
		if(t.getClass().getSuperclass().equals(BaseModel.class)){
			Field[] fields = t.getClass().getSuperclass().getDeclaredFields();
			for(int i=0,length=fields.length;i<length;i++){
				Field field = fields[i];
				if(!field.getName().equals(SERIAL_VERSION_UID_FIELD_NAME)){
					Object value = getValue(t,field);
					if(value != null){
						bma.put(getFieldName(field), value);
					}
				}
			}
			if(bma.get(BaseModelTableName.ID.toStringValue()) == null){
				bma.put(BaseModelTableName.ID.toStringValue(), getUUID());
			}
			if(bma.get(BaseModelTableName.CREATED_TIME.toStringValue()) == null){
				bma.put(BaseModelTableName.CREATED_TIME.toStringValue(), new Date());
			}
		}
		return bma;
	}
	
	private static <T> Map<String, Object> getUpdateBaseModelAttributes(T t){
		Map<String, Object> bma = new HashMap<String, Object>();
		if(t.getClass().getSuperclass().equals(BaseModel.class)){
			Field[] fields = t.getClass().getSuperclass().getDeclaredFields();
			for(int i=0,length=fields.length;i<length;i++){
				Field field = fields[i];
				if(!field.getName().equals(SERIAL_VERSION_UID_FIELD_NAME)){
					Object value = getValue(t,field);
					if(value != null){
						bma.put(getFieldName(field), value);
					}
				}
			}
			if(bma.get(BaseModelTableName.UPDATED_TIME.toStringValue()) == null){
				bma.put(BaseModelTableName.UPDATED_TIME.toStringValue(), new Date());
			}
		}
		return bma;
	}
	
	private static String toChangeFormat(String field){
		String value = field.replaceAll("[A-Z]", "_$0").
				replaceFirst("[0-9]", "_$0").toLowerCase();
		return value.substring(0,1).equals("_")?value.substring(1, value.length()):value;
	}
	
	/**
	 * To first letter upper case.
	 * @param str
	 * @return
	 */
	private static String toFirstLetterUpperCase(String str){
		if(str!=null && !str.equals("")){
			String firstLetter = str.substring(0, 1).toUpperCase();
			return firstLetter + str.substring(1, str.length());
		}else{
			return str;
		}
	}
	
	/**
	 * Get UUID.
	 * @return
	 */
	private static String getUUID(){
		return UUID.randomUUID().toString().replace("-","");
	}
	
	public static String printSQL(String sql, StatementParameter param) {
		int i = 0;
		while (sql.indexOf('?') > -1) {
			if (param == null) {
				throw new RuntimeException("没有设置参数.");
			}
			if (i >= param.size()) {
				return sql;
			}
			String value;
			int type = param.getTypes(i);

			if (type == Types.VARCHAR) {
				value = "'" + param.getString(i) + "'";
			} else if (type == Types.TIME) {
				value = "'" + getTime(param.getTime(i)) + "'";
			} else if (type == Types.DATE || type == Types.TIMESTAMP) {
				value = "'" + getTime(param.getDate(i)) + "'";
			} else if (type == Types.INTEGER) {
				value = Integer.toString(param.getInt(i));
			} else if (type == Types.FLOAT) {
				value = Float.toString(param.getFloat(i));
			} else if (type == Types.DOUBLE) {
				value = Double.toString(param.getDouble(i));
			} else if (type == Types.BIGINT) {
				value = Long.toString(param.getLong(i));
			} else if (type == Types.NULL) {
				value = null;
			} else {
				throw new RuntimeException("未知数据类型[" + type + "]");
			}

			sql = sql.substring(0, sql.indexOf('?')) + value + sql.substring(sql.indexOf('?') + 1, sql.length());
			i++;
		}
		return sql;//
	}
	
	private static String getTime(Date date) {
		DateFormat df = new SimpleDateFormat(DateUtil.DATETIME_PATTERN_DEFAULT);
		return df.format(date);
	}

	private static String getTime(Time time) {
		DateFormat df = new SimpleDateFormat(DateUtil.TIME_PATTERN_HHMMSS);
		return df.format(time);
	}
}